/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Schema;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import org.apache.commons.mail.DefaultAuthenticator;
import org.apache.commons.mail.Email;
import org.apache.commons.mail.EmailException;
import org.apache.commons.mail.SimpleEmail;

/**
 *
 * @author Lars Malmqvist
 */

@ManagedBean(name="schema")
@RequestScoped
public class Schema {

    private String epost;
    private int month;
    private int week;
    private static Calendar cal = Calendar.getInstance();
    private ArrayList<String> employees;
    private ArrayList<String> messages;
    private String thisUser;
    private Connection connection;
    private String url;
    private String username;
    private String password;
    private String color;

    /**
     * Creates a new instance of Schema
     * @throws java.sql.SQLException
     */
    
    public Schema() throws SQLException {
        messages = new ArrayList<>();
        employees = new ArrayList<>();
        url = "jdbc:mysql://pellos.no-ip.org:3306/antons_skafferi";
        username = "anders";
        password = "anton";
        connection = DriverManager.getConnection(url, username, password);
    }
    
    public void setWeek(int weekUpdater) {
        
        cal.set(Calendar.WEEK_OF_YEAR, getWeek());
        cal.add(Calendar.WEEK_OF_YEAR, weekUpdater);  
    
    }
    
    public int getWeek() {
        
        return cal.get(Calendar.WEEK_OF_YEAR);
    
    }
    
    public String currentDatum(int dayOfWeek) {
        
        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.WEEK_OF_YEAR, getWeek());
        calendar.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
        calendar.add(Calendar.DAY_OF_WEEK, dayOfWeek);
        Date now = calendar.getTime();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String date = sdf.format(now);
        return date;
        
    }
    
    public void setDatumInClassDatum(int dayOfWeek, String typeOfPass){
        String tempDate = currentDatum(dayOfWeek);
        Datum.setDate(tempDate);
        Datum.setType(typeOfPass);
    }
    
    public String color(int dayOfWeek, String typeOfPass, String thisUser) throws SQLException {
        
        String color = getStatus(dayOfWeek, typeOfPass, thisUser);
        
        if (color.equals("green")) {
            return "green";
        }
        
        else if (color.equals("orange")) {
            return "orange";
        }
        
        else if (color.equals("blue")) {
            return "blue";
        }
        
        else {
            return "white";
        }
            
    }
    
    public String getStatus(int dayOfWeek, String typeOfPass, String thisUser) throws SQLException {
        
        String date = currentDatum(dayOfWeek);
        String color = null;
        
        PreparedStatement getStatus = connection.prepareStatement("SELECT status FROM Shifts WHERE shift_time='" + typeOfPass + "' AND employee_name='" + thisUser + "' AND shift_date='" + date + "'");
        ResultSet result = getStatus.executeQuery();
        
        if (result.next()) {
            color = result.getString("status");
            return color;
        }
        
        else {
            return "white";
        }
    
    }
    
    public void sendMailAndUpdateDB(String date, String thisUser, String reciever, String typeOfPass) throws EmailException, SQLException{
        
        /*
        String epostAdress = reciever;
        epostAdress += "@antons.se";
        
        Email email = new SimpleEmail();
        email.setHostName("smtp.gmail.com");
        email.setSmtpPort(587);
        email.setAuthenticator(new DefaultAuthenticator("antons.passbyten", "stege123"));
        email.setStartTLSEnabled(true);
        email.setFrom("antons.passbyten@gmail.com");
        email.setSubject("" + thisUser + " vill byta pass med dig");
        email.setMsg("" + thisUser + "vill byta plats med dig den " + date + ".");
        email.addTo(epostAdress);
        email.send();
        */
        
        PreparedStatement createMessage = connection.prepareStatement("INSERT INTO messages (sender, reciever, date) VALUES ('" + thisUser + "', '" + reciever + "', '" + date + "')");
        createMessage.execute();

        PreparedStatement setSenderStatus = connection.prepareStatement("UPDATE Shifts SET status='orange' WHERE employee_name='" + thisUser + "' AND shift_date='" + date + "' AND shift_time='" + typeOfPass + "'");
        setSenderStatus.execute();
        
        PreparedStatement setRecieverStatus = connection.prepareStatement("INSERT INTO Shifts (shift_date, shift_time, employee_name, status) VALUES ('" + date + "', '" + typeOfPass + "', '" + reciever + "', 'blue')");
        setRecieverStatus.execute();
        
    }
    
    public void acceptChangeShift(String date, String thisUser, String typeOfPass) throws SQLException {
        
        PreparedStatement changeRecieverStatus = connection.prepareStatement("UPDATE Shifts SET status='green' WHERE employee_name='" + thisUser + "' AND shift_date='" + date + "' AND shift_time='" + typeOfPass + "'");
        changeRecieverStatus.execute();    
        
        PreparedStatement findSender = connection.prepareStatement("SELECT sender FROM messages WHERE reciever='" + thisUser + "' AND date='" + date + "'");
        ResultSet result = findSender.executeQuery();
        
        String sender = null;
        
        while (result.next()) {
            sender = result.getString("sender");
        }
        
        PreparedStatement changeSenderStatus = connection.prepareStatement("DELETE FROM Shifts WHERE employee_name='" + sender + "' AND shift_date='" + date + "' AND shift_time='" + typeOfPass + "'");
        changeSenderStatus.execute();
        
        PreparedStatement deleteFromMessages = connection.prepareStatement("DELETE FROM messages WHERE reciever='" + thisUser + "' AND date='" + date + "'");
        deleteFromMessages.execute();        
        
    }
    
    public void declineChangeShift(String date, String thisUser, String typeOfPass) throws SQLException {
    
        PreparedStatement changeRecieverStatus = connection.prepareStatement("DELETE FROM Shifts WHERE employee_name='" + thisUser + "' AND shift_date='" + date + "' AND shift_time='" + typeOfPass + "'");
        changeRecieverStatus.execute();
        
        PreparedStatement findSender = connection.prepareStatement("SELECT sender FROM messages WHERE reciever='" + thisUser + "' AND date='" + date + "'");
        ResultSet result = findSender.executeQuery();
        
        String sender = null;
        
        while (result.next()) {
            sender = result.getString("sender");
        }
        
        PreparedStatement changeSenderStatus = connection.prepareStatement("UPDATE Shifts SET status='green' WHERE employee_name='" + sender + "' AND shift_date='" + date + "' AND shift_time='" + typeOfPass + "'");
        changeSenderStatus.execute();
        
        PreparedStatement deleteFromMessages = connection.prepareStatement("DELETE FROM messages WHERE reciever='" + thisUser + "' AND date='" + date + "'");
        deleteFromMessages.execute();
                
    
    }
    
    public ArrayList<String> displayEmployees(String thisUser) throws SQLException{
        
        PreparedStatement getEmployees = connection.prepareStatement("SELECT * FROM Shifts");
        ResultSet result = getEmployees.executeQuery();
        String employee = null;
        
        while (result.next()){
            employee = result.getString("employee_name");
            if (!(employees.contains(employee)) && (employee == null ? thisUser != null : !employee.equals(thisUser))) {
                employees.add(employee);
            }
        }
        
        return employees;
        
    }
    
    public ArrayList<String> displayMessages() throws SQLException {
        
        PreparedStatement getMessagesTable = connection.prepareStatement("SELECT * FROM messages");
        ResultSet results = getMessagesTable.executeQuery();
        
        String sender = null;
        String reciever = null;
        String date = null;
        String message = null;
        
        while (results.next()){
            sender = results.getString("sender");
            reciever = results.getString("reciever");
            date = results.getString("date");
            message = sender + " vill byta pass med " + reciever + " den " + date;
            if (!(messages.contains(message))) {
                messages.add(message);
            }
        }
        
        return messages;
        
    }
    
}
